insert overwrite table TBL_ORDERDETAILS_ALL
select ORDERDETAILS_ID    ,
VERSION            ,
NOWDATE            ,
VIRTUALGOODSID     ,
RETURNCODE         ,
LITRE              ,
if(STATIONCODE = '58008','58010',STATIONCODE)       ,
COUPONAMOUNT       ,
REQUESTID          ,
ORDERNUM           ,
REALAMOUNT         ,
OILCODE            ,
ORDER_DEAL         ,
DISCOUNTAMOUNT     ,
CARDUSERID         ,
AMOUNT             ,
OILPRICE           ,
TERMNUM            ,
SENDTIME           ,
INVOICETITLE       ,
MEMBERNO           ,
VIRTUALGOODSIDS    ,
CUSTOMER_ID        ,
create_shijian     ,
PPSORDERNUMBER     ,
GOODSAMOUNT        ,
FUELCOUPONAMOUNT   ,
GOODCOUPONAMOUNT   ,
GOODSCOUPONAMOUNT  ,
UPDATEREALAMOUNT   ,
FULECOUPONAMOUNT   ,
FULEAMOUNT         ,
CARLICENSE         ,
OILGUNNO           ,
ORDER_STATE        ,
EPSCODE  from mid.TBL_ORDERDETAILS union all
select  ORDERDETAILS_ID    ,
VERSION            ,
NOWDATE            ,
VIRTUALGOODSID     ,
RETURNCODE         ,
LITRE              ,
if(STATIONCODE = '58008','58010',STATIONCODE)       ,
COUPONAMOUNT       ,
REQUESTID          ,
ORDERNUM           ,
REALAMOUNT         ,
OILCODE            ,
ORDER_DEAL         ,
DISCOUNTAMOUNT     ,
CARDUSERID         ,
AMOUNT             ,
OILPRICE           ,
TERMNUM            ,
SENDTIME           ,
INVOICETITLE       ,
MEMBERNO           ,
VIRTUALGOODSIDS    ,
CUSTOMER_ID        ,
create_shijian     ,
PPSORDERNUMBER     ,
GOODSAMOUNT        ,
FUELCOUPONAMOUNT   ,
GOODCOUPONAMOUNT   ,
GOODSCOUPONAMOUNT  ,
UPDATEREALAMOUNT   ,
FULECOUPONAMOUNT   ,
FULEAMOUNT         ,
CARLICENSE         ,
OILGUNNO           ,
ORDER_STATE        ,
EPSCODE from mid.TBL_ORDERDETAILS_CD;

insert overwrite  table transactions_vip  
select B.CARDUSERID,A.*
from  mid.transactions as A
join TBL_ORDERDETAILS_ALL as B
on A.transaction_number = B.ORDERNUM and A.station_id = B.STATIONCODE and A.sale_total = B.AMOUNT/100 AND ORDER_DEAL = 4 and substr(A.transaction_date,1,10) = substr(B.SENDTIME,1,10);

INSERT OVERWRITE TABLE transaction_items_mop_vip
select A.carduser_id,B.*
from transactions_vip as A
JOIN mid.transaction_items_mop as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

INSERT OVERWRITE TABLE transaction_items_notoil_vip
select A.carduser_id,B.*
from transactions_vip as A
JOIN mid.transaction_items_notoil as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

INSERT OVERWRITE TABLE transaction_items_oil_vip
select A.carduser_id,B.*
from transactions_vip as A
JOIN mid.transaction_items_oil as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id
where B.item_type!=20 and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600;

insert overwrite table res_oil_vip
select substr(SENDTIME,1,16),trim(STATIONCODE),trim(OILCODE),COUNT(*),SUM(LITRE)/1000,SUM(REALAMOUNT)/100
FROM TBL_ORDERDETAILS_ALL WHERE LITRE != 0 and ORDER_DEAL = 4 GROUP BY substr(SENDTIME,1,16),trim(STATIONCODE),trim(OILCODE);

insert overwrite table res_notoil_vip
select substr(Z.transaction_date,1,16),Y.station_id,count(DISTINCT Z.transaction_id),sum(Y.item_value)
from transactions_vip as Z
join transaction_items_notoil_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600
group by substr(Z.transaction_date,1,16),Y.station_id;
-- select substr(SENDTIME,1,16),STATIONCODE,COUNT(*),SUM(REALAMOUNT)/100
-- FROM TBL_ORDERDETAILS_ALL WHERE LITRE = 0 and ORDER_DEAL = 4 GROUP BY substr(SENDTIME,1,16),STATIONCODE;

insert overwrite table res_notoilDepartment_vip
select substr(X.transaction_date,1,16),X.station_id,X.dept_name,count(X.department_id),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value,Y.department_id,C.dept_name
from transactions_vip as Z 
join transaction_items_notoil_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
join mid.products as B
on Y.product_code = B.product_code and  B.station_id = 50006
join mid.departments as C
on C.station_id = 50006 and B.department_id = C.department_id
where unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600
)AS X
group by
substr(X.transaction_date,1,16),X.station_id,X.dept_name;

insert overwrite table mid_notoilTop_vip
select substr(X.transaction_date,1,10),X.station_id,X.item_description,X.product_code,sum(X.item_quantity),sum(X.item_value)
from
(select Y.transaction_id,Z.transaction_date,Y.station_id,Y.item_description,Y.item_value,Y.product_code,Y.item_quantity
from transactions_vip as Z 
join transaction_items_notoil_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where  unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id,X.item_description,X.product_code;

insert overwrite table res_notoilTop_vip
select * from
(select minutes,stationID,top10,productCode,number,money,
row_number() over (partition by minutes,stationID order by number desc)AS rank from mid_notoilTop_vip a
)b
where b.rank<=10;

insert OVERWRITE table mid_lube_vip
select DISTINCT d.transaction_date,d.station_id,d.transaction_id,d.item_value,d.oillitre from 
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value ,trim(A.item_description)as oillitre
from transaction_items_notoil_vip as A
join mid.products as B
on A.product_code = B.product_code and B.station_id = 50006
join transactions_vip as C
on C.transaction_id = A.transaction_id  and C.station_id=A.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(C.transaction_date)>-24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(C.transaction_date)<24*3600
)d where d.department_id in (548,64);

insert overwrite table res_lube_vip
select substr(minutes,1,16),stationID,count(DISTINCT transactionID),sum(substr(oillitre,-2,1)),sum(itemvalue)
from mid_lube_vip
group by substr(minutes,1,16),stationID;

-- insert overwrite table mid_notoilRate_vip
-- select substr(transaction_date,1,13),station_id,count(*)
-- from transactions_vip
-- group by substr(transaction_date,1,13),station_id;
--
-- insert overwrite table res_notoilRate_vip
-- select A.houra,A.stationID,A.numbera,B.numberb
-- FROM
-- (select SUBSTR(minutes,1,13)AS houra,stationID,sum(notoilNumber)AS numbera
-- from res_notoil_vip
-- group by SUBSTR(minutes,1,13),stationID) AS A
-- JOIN
-- (select substr(minutes,1,13)AS hourb,stationID,sum(tradeNumber)AS numberb
-- from mid_notoilRate_vip
-- group by substr(minutes,1,13),stationID) AS B
-- ON A.houra=B.hourb and A.stationID=B.stationID;

insert overwrite table res_notoilRate_vip
SELECT substr(minutes,1,13),stationID,
sum(case when is_oil=1 then 0 else numbera end)
,sum(numbera) from
(SELECT minutes,stationID,oilNumber numbera,1 is_oil from res_oil_vip
union all
select minutes,stationID,notoilNumber numbera,0 is_oil
from res_notoil_vip) tmp
group by SUBSTR(minutes,1,13),stationID;

insert overwrite table res_mop_vip
select substr(X.transaction_date,1,10),X.station_id,X.mop_name,count(X.item_quantity),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.mop_name,Y.item_value,Y.item_quantity
from transactions_vip as Z 
join transaction_items_mop_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
where Y.item_value > 0
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600)AS X
group by substr(X.transaction_date,1,10),X.station_id,X.mop_name;

insert overwrite table res_HHTIPT_vip
select B.substrb,B.station_id,A.count,A.sum,B.count-A.count,B.sum-A.sum,B.count,B.sum
from
(select substr(transaction_date,1,13)as substrb,station_id,count(transaction_id)as count,sum(sale_total)as sum
from transactions_vip
group by substr(transaction_date,1,13),station_id)as B
join
(select substr(Z.transaction_date,1,13)as substra,Z.station_id,count(Z.transaction_id)as count,sum(Z.sale_total)as sum
from
transactions_vip AS Z
JOIN mid.cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT'
group by substr(Z.transaction_date,1,13),Z.station_id) as A
on A.substra=B.substrb AND A.station_id=B.station_id;

set hive.auto.convert.join=false;

insert overwrite table res_mopipt_vip
select substr(B.transaction_date,1,10),A.station_id,A.mop_name,count(A.item_quantity),sum(A.item_value)
from transaction_items_mop_vip as A
join
(select Z.station_id,Z.transaction_id,Z.transaction_date
from
transactions_vip AS Z
JOIN mid.cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name != 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id and A.item_value > 0
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600
group by substr(B.transaction_date,1,10),A.station_id,A.mop_name;

set hive.auto.convert.join=false;-----

insert overwrite table res_mophht_vip
select substr(B.transaction_date,1,10),A.station_id,A.mop_name,count(A.item_quantity),sum(A.item_value)
from transaction_items_mop_vip as A
join
(select Z.station_id,Z.transaction_id,Z.transaction_date
from
transactions_vip AS Z
JOIN mid.cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id
where A.item_value > 0
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600
group by substr(B.transaction_date,1,10),A.station_id,A.mop_name;

insert OVERWRITE table mid_exceptLube_vip
select DISTINCT d.transaction_date,d.station_id,d.transaction_id,d.item_value from 
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value 
from transaction_items_notoil_vip as A
join mid.products as B
on A.product_code = B.product_code and B.station_id = 50006
join mid.transactions as C
on C.transaction_id = A.transaction_id and C.station_id = A.station_id
where unix_timestamp(A.transaction_date)-unix_timestamp(C.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(C.transaction_date)>-24*3600
)d where d.department_id not in (548,64);

insert overwrite table res_exceptLube_vip
select substr(minutes,1,16),stationID,count(DISTINCT transactionID),sum(itemvalue)
from mid_exceptLube_vip
group by substr(minutes,1,16),stationID;

